﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Security;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Linq;

namespace Pakxel.Framework
{
    public sealed class RProvider : RoleProvider
    {
        private const string FILE_SETTINGS_NAME = "settings.xml";
        private ConnectionStringSettings pConnectionStringSettings;
        private string ConnectionString=string.Empty;

        #region Propiedades

        private string pApplicationName;
        public override string ApplicationName
        {
            get { return pApplicationName; }
            set { pApplicationName = value; }
        }

        // If false, exceptions are thrown to the caller. If true, exceptions are written to the event log.
        private bool pWriteExceptionsToEventLog = false;
        public bool WriteExceptionsToEventLog
        {
            get { return pWriteExceptionsToEventLog; }
            set { pWriteExceptionsToEventLog = value; }
        }
        #endregion
        /// <summary>
        /// A helper function that writes exception detail to the event log. Exceptions
        /// are written to the event log as a security measure to avoid private database
        /// details from being returned to the browser. If a method does not return a status
        /// or boolean indicating the action succeeded or failed, a generic exception is also 
        /// thrown by the caller.
        /// </summary>
        /// <param name="e">Exception</param>
        /// <param name="action">Action</param>
        private static void WriteToEventLog(SqlException e, string action)
        {
            using (System.Diagnostics.EventLog log = new System.Diagnostics.EventLog { Source = "RProvider", Log = "Application" })
            {
                log.WriteEntry(string.Format("An exception occurred. Please check the Event Log.\n\nAction: {0}\n\nException: {1}",
                    action, e));
            }
        }

        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            // Verify that config isn't null
            if (config == null)
                throw new ArgumentNullException("config");

            // Assign the provider a default name if it doesn't have one
            if (String.IsNullOrEmpty(name))
                name = "SmartlogisticsRoleProvider";

            // Add a default "description" attribute to config if the attribute doesn't exist or is empty
            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Custom SmartLogistics SQL Role Provider");
            }

            // Call the base class's Initialize method
            base.Initialize(name, config);

            if (config["applicationName"] == null || config["applicationName"].Trim() == "")
                pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
            else
                pApplicationName = config["applicationName"];

            if (config["writeExceptionsToEventLog"] != null)
                pWriteExceptionsToEventLog = (config["writeExceptionsToEventLog"].ToUpper() == "TRUE");

            // Initialize SqlConnection.
            pConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if (pConnectionStringSettings == null || pConnectionStringSettings.ConnectionString.Trim() == "")
                throw new ProviderException("La cadena de conexion no puede ser nula.");

            IDatabaseConnection IConn = new DatabaseConnection(XElement.Load(System.Environment.CurrentDirectory + "\\" + FILE_SETTINGS_NAME));
            ConnectionString = IConn.ConnectionString("PRODUCCION"); 
        }

        private void ExecItemsIn(SqlCommand Cmd, string[] ListTo, string[] ListIn, string Action)
        {
            //using (SqlConnection cnn = new SqlConnection(pConnectionStringSettings.ConnectionString))
            using (SqlConnection cnn = new SqlConnection(ConnectionString))
            {
                Cmd.Connection = cnn;

                SqlTransaction tran = null;
                try
                {
                    cnn.Open();
                    tran = cnn.BeginTransaction();
                    Cmd.Transaction = tran;

                    foreach (string ItmTo in ListTo)
                        foreach (string ItmIn in ListIn)
                        {
                            Cmd.Parameters[0].Value = ItmTo; //Parametro de objecto de lista.
                            Cmd.Parameters[1].Value = ItmIn; //Parametro de objecto para la lista.
                            Cmd.ExecuteNonQuery();
                        }

                    tran.Commit();
                }
                catch (SqlException ex)
                {
                    try { tran.Rollback(); }
                    catch
                    {
                        if (WriteExceptionsToEventLog)
                            WriteToEventLog(ex, Action);
                        else
                            throw ex;
                    }

                }
                finally { cnn.Close(); }
            }
        }

        public override void AddUsersToRoles(string[] usernames, string[] roleNames)
        {
            foreach (string rolename in roleNames)
            {
                if (!RoleExists(rolename))
                    throw new ProviderException("Rol no encontrado: " + rolename);
            }

            foreach (string username in usernames)
            {
                if (username.Contains(","))
                    throw new ArgumentException(string.Format("El nombre de usuario '{0}' es invalido.", username));

                foreach (string rolename in roleNames)
                {
                    if (IsUserInRole(username, rolename))
                        throw new ProviderException(string.Format("El usuario '{0}' ya se encuentra en el Rol '{1}'", username, rolename));
                }
            }

            using (SqlCommand cmd = new SqlCommand("INSERT INTO T015 (T003Rol, T002UserName) Values(@rolename, @username)"))
            {
                cmd.Parameters.Add("@username", SqlDbType.VarChar, 15);
                cmd.Parameters.Add("@rolename", SqlDbType.VarChar, 10);

                ExecItemsIn(cmd, usernames, roleNames, "AddUsersToRoles");
            }
        }

        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            using (SqlCommand cmd = new SqlCommand("DELETE FROM T015 WHERE T002UserName = @Username AND T003Rol = @Rolename"))
            {
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 15);
                cmd.Parameters.Add("@Rolename", SqlDbType.VarChar, 10);

                ExecItemsIn(cmd, usernames, roleNames, "RemoveUsersFromRoles");
            }
        }

        /// <summary>Crea un nuevo Rol.</summary>
        /// <param name="roleName">Nombre del nuevo Rol</param>
        /// <param name="description">Descripcion del nuevo Rol.</param>
        public void CreateRole(string roleName, string description)
        { 
            if (roleName.Contains(","))
                throw new ArgumentException("El rol no debe contener comas.");

            if (RoleExists(roleName))
                throw new ProviderException("El rol ya existe.");

            //using (SqlConnection cnn = new SqlConnection(pConnectionStringSettings.ConnectionString))
            using (SqlConnection cnn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("INSERT INTO T003 (T003Rol, T003Descripcion) Values(@rolename, @descripcion)", cnn);

                cmd.Parameters.Add("@rolename", SqlDbType.VarChar, 10).Value = roleName;
                cmd.Parameters.Add("@descripcion", SqlDbType.VarChar, 20).Value = description;

                try
                {
                    cnn.Open();

                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    if (WriteExceptionsToEventLog)
                        WriteToEventLog(ex, "CreateRole");
                    else
                        throw ex;
                }
                finally { cnn.Close(); }
            }
        }

        public override void CreateRole(string roleName)
        {
            throw new NotImplementedException("No se permite ingresar un Rol si descripcion.");
        }

        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            if (!RoleExists(roleName))
                throw new ProviderException("El Rol no existe");

            if (throwOnPopulatedRole)
                if (GetUsersInRole(roleName).Length > 0) throw new ProviderException("Este rol tiene usuarios.");

            //using (SqlConnection conn = new SqlConnection(pConnectionStringSettings.ConnectionString))
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlParameter ParRole = new SqlParameter("@Rolename", SqlDbType.VarChar, 10) { Value = roleName };
                SqlCommand DelRole = new SqlCommand("DELETE FROM T003 WHERE T003Rol = @Rolename", conn);
                DelRole.Parameters.Add(ParRole);

                //Crear Trigger para la eliminacion de Users in roles
                SqlCommand DelUsrRole = new SqlCommand("DELETE FROM T015 WHERE T003Rol = @Rolename", conn);
                DelUsrRole.Parameters.Add(ParRole);

                SqlTransaction tran = null;
                try
                {
                    conn.Open();
                    tran = conn.BeginTransaction();
                    DelRole.Transaction = tran;
                    DelUsrRole.Transaction = tran;
                    DelUsrRole.ExecuteNonQuery();
                    DelRole.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (SqlException e)
                {
                    try { tran.Rollback(); }
                    catch
                    {
                        if (WriteExceptionsToEventLog)
                        {
                            WriteToEventLog(e, "DeleteRole");
                            return false;
                        }
                        else
                            throw e;
                    }
                }
                finally { conn.Close(); }
            }

            return true;
        }

        private string[] GetListFromQuery(SqlCommand Cmd, string Action)
        {
            string tmpList = "";

            //using (SqlConnection conn = new SqlConnection(pConnectionStringSettings.ConnectionString))
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                Cmd.Connection = conn;
                SqlDataReader reader = null;

                try
                {
                    conn.Open();

                    reader = Cmd.ExecuteReader();

                    while (reader.Read())
                        tmpList += reader.GetString(0) + ",";
                }
                catch (SqlException ex)
                {
                    if (WriteExceptionsToEventLog)
                        WriteToEventLog(ex, Action);
                    else
                        throw ex;
                }
                finally
                {
                    if (reader != null) { reader.Close(); }

                    conn.Close();
                }

            }

            return (tmpList.Length > 0 ? 
                tmpList.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) : 
                new string[0]);
        }

        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            using (SqlCommand cmd = new SqlCommand("SELECT T002UserName FROM T015 WHERE T002UserName LIKE @UsernameSearch AND T003Rol = @RoleName"))
            {
                cmd.Parameters.Add("@UsernameSearch", SqlDbType.VarChar, 15).Value = usernameToMatch;
                cmd.Parameters.Add("@RoleName", SqlDbType.VarChar, 10).Value = roleName;

                return GetListFromQuery(cmd, "FindUsersInRole");
            }
        }

        public override string[] GetAllRoles()
        {
            using (SqlCommand cmd = new SqlCommand("SELECT T003Rol FROM T003"))
            {
                return GetListFromQuery(cmd, "GetAllRoles");
            }
        }

        public override string[] GetRolesForUser(string username)
        {
            using (SqlCommand cmd = new SqlCommand("SELECT T003Rol FROM T015 WHERE T002UserName = @Username"))
            {
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 15).Value = username;

                return GetListFromQuery(cmd, "GetRolesForUser");
            }
        }

        public override string[] GetUsersInRole(string roleName)
        {
            using (SqlCommand cmd = new SqlCommand("SELECT T002Username FROM T015 WHERE T003Rol = @Rolename"))
            {
                cmd.Parameters.Add("@Rolename", SqlDbType.VarChar, 10).Value = roleName;

                return GetListFromQuery(cmd, "GetUsersInRole");
            }
        }

        private bool ExistsFromQuery(SqlCommand Cmd, string Action)
        {
            //using (SqlConnection conn = new SqlConnection(pConnectionStringSettings.ConnectionString))
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                Cmd.Connection = conn;
                try
                {
                    conn.Open();

                    return ((int)Cmd.ExecuteScalar() > 0);
                }
                catch (SqlException ex)
                {
                    if (WriteExceptionsToEventLog)
                        WriteToEventLog(ex, Action);
                    else
                        throw ex;
                }
                finally { conn.Close(); }
            }

            return false;
        }

        public override bool RoleExists(string roleName)
        {
            using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM T003 WHERE T003Rol = @Rolename"))
            {
                cmd.Parameters.Add("@Rolename", SqlDbType.VarChar, 10).Value = roleName;
                return ExistsFromQuery(cmd, "RoleExists");
            }
        }

        public override bool IsUserInRole(string username, string roleName)
        {
            using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM T015 WHERE T002UserName = @Username AND T003Rol = @Rolename"))
            {
                cmd.Parameters.Add("@Username", SqlDbType.VarChar, 15).Value = username;
                cmd.Parameters.Add("@Rolename", SqlDbType.VarChar, 10).Value = roleName;

                return ExistsFromQuery(cmd, "IsUserInRole");
            }
        }
    }
}
